﻿Imports System.IO
Imports System.Data.SQLite

Public Module SQLiteUtilities
	'http://sqlite.phxsoftware.com/forums/t/941.aspx
	Public Sub BackupDatabase(backupStream As Stream)
		Using myStream As New StreamWriter(backupStream)

			Dim tables As New List(Of String)

			Using sqlCmd As New SQLiteCommand
				Using sqlConn As New SQLiteConnection(My.Settings.CongregationDataConnectionString)
					sqlConn.Open()
					sqlCmd.Connection = sqlConn

					sqlCmd.CommandText = "SELECT name FROM sqlite_master WHERE sql NOT NULL AND type = 'table' AND name <> 'sqlite_sequence'"
					Using sqlReader As SQLiteDataReader = sqlCmd.ExecuteReader
						While sqlReader.Read
							tables.Add(sqlReader("name"))
						End While
					End Using

					For Each table In tables
						sqlCmd.CommandText = String.Format("PRAGMA table_info(""{0}"")", table)
						Dim tableColumns As New List(Of String)
						Dim quotedTableColumns As New List(Of String)

						Using sqlReader As SQLiteDataReader = sqlCmd.ExecuteReader
							While sqlReader.Read
								tableColumns.Add(sqlReader("name"))
								'the quote("") statement will make sure that the value is quoted if needed on our select statement
								quotedTableColumns.Add(String.Format("quote(""{0}"")", sqlReader("name")))
							End While
						End Using

						Dim insertStatement As String = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, String.Join(",", tableColumns), "{0}")

						sqlCmd.CommandText = String.Format("SELECT {0} FROM {1}", String.Join(",", quotedTableColumns), table)
						Using sqlReader As SQLiteDataReader = sqlCmd.ExecuteReader
							Dim rowValues As List(Of String)
							While sqlReader.Read
								rowValues = New List(Of String)
								For x = 0 To sqlReader.FieldCount - 1
									rowValues.Add(sqlReader(x))
								Next
								myStream.WriteLine(String.Format(insertStatement, String.Join(",", rowValues)))
							End While
						End Using

					Next
				End Using
			End Using

		End Using

	End Sub

	Public Sub RestoreDatabase(restoreStream As Stream)
		Using myStream As New StreamReader(restoreStream)

			Dim tables As New List(Of String)

			Using sqlCmd As New SQLiteCommand
				Using sqlConn As New SQLiteConnection(My.Settings.CongregationDataConnectionString)
					sqlConn.Open()
					sqlCmd.Connection = sqlConn

					sqlCmd.CommandText = "SELECT name FROM sqlite_master WHERE sql NOT NULL AND type = 'table' AND name <> 'sqlite_sequence'"
					Using sqlReader As SQLiteDataReader = sqlCmd.ExecuteReader
						While sqlReader.Read
							tables.Add(sqlReader("name"))
						End While
					End Using

					For Each table In tables
						sqlCmd.CommandText = String.Format("DELETE FROM {0}", table)
						sqlCmd.ExecuteNonQuery()
					Next

					Using sqlTrans As SQLiteTransaction = sqlConn.BeginTransaction
						While Not myStream.EndOfStream
							Dim line As String = myStream.ReadLine

							'fix for a spelling mistake in the database
							line = line.Replace("IsAnnointed", "IsAnointed")

							sqlCmd.CommandText = line
							sqlCmd.ExecuteNonQuery()
						End While
						sqlTrans.Commit()
					End Using

				End Using
			End Using
		End Using

	End Sub
End Module
